package org.chatboleh.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.chatboleh.database.ConnectionFactory;
import org.chatboleh.exception.EDMException;

public abstract class MasterBean {
	protected Logger logger = Logger.getLogger(this.getClass());
	// private static final String CONTEXT_NAME = "java:/comp/env";
	// private static final String RESOURCE_NAME = "jdbc/wcmsdb";
	private static final String URI = getDbUri();
	private static final String DB_NAME = "edmdb2";
	private static final String DB_USERNAME = "sa";
	private static final String DB_PASSWORD = "";

	protected long id;

	protected ResultSet resultset;

	private Connection conn;

	protected PreparedStatement ps;

	/**
	 * Don't need to do anything here.
	 */
	public MasterBean() {
		this.id = -1;
	}

	private static String getDbUri() {
		String uri = "jdbc:hsqldb:hsql://localhost/edbdb2";
//		try {
//			uri += new File(".").getCanonicalPath() + "/" + DB_NAME;
//		} catch (IOException e) {
//			// do nothing
//		}
		return uri;
	}

	public MasterBean(long id) {
		this.id = id;
		try {
			this.getData();
		} catch (EDMException e) {
			logger.error(e);
		}
	}

	protected void closeStatement() throws EDMException {
		try {
			if (ps != null) {
				ps.close();
				ps = null;
			}
//			if (conn != null) {
//				conn.close();
//				conn = null;
//			}
		} catch (SQLException e) {
			throw new EDMException(e);
		}
	}

	public void delete() throws EDMException {
		try {
			openConnection();
			prepareStatement(deletequery());
			executeUpdate();
		} finally {
			closeStatement();
		}
	}

	/**
	 * 
	 * @return "DELETE FROM " + tablename() + " WHERE id = " + id;
	 */
	protected String deletequery() {
		return "DELETE FROM " + tablename() + " WHERE id = " + id;
	}

	protected void executeQuery() throws EDMException {
		try {
//			logger.debug("running... ");
			resultset = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new EDMException(e);
		}
	}

	protected void executeUpdate() throws EDMException {
		try {
			logger.debug("running... ");
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new EDMException(e);
		}
	}

	/**
	 * Field names, with "," in between. Ignore id
	 * 
	 * @return
	 */
	protected abstract String fieldnames();

	private void getData() throws EDMException {
		logger.debug("running... ");
		try {
			openConnection();
			prepareStatement(selectquery());
			executeQuery();
			if (resultset.next()) {
				loadResultSetIntoObject(resultset);
			}
		} catch (SQLException e) {
			throw new EDMException(e);
		} finally {
			closeStatement();
		}
	}

	public abstract List<?> getList(String criteria) throws EDMException;
	
	protected abstract void loadResultSetIntoObject(ResultSet resultset);

	public long getId() {
		return id;
	}

	protected void openConnection() throws EDMException {
//		logger.debug("running... ");
//		try {
			conn = ConnectionFactory.getInstance();
//			Class.forName("org.hsqldb.jdbcDriver");
//			conn = DriverManager.getConnection(URI, DB_USERNAME, DB_PASSWORD);
			// conn =
			// DriverManager.getConnection("jdbc:hsqldb:hsql://localhost",
			// "sa", "");
//		} catch (Exception e) {
//			throw new EDMException(e);
//		}
		// try {
		// Context initContext = new InitialContext();
		// Context envContext = (Context) initContext.lookup(CONTEXT_NAME);
		// DataSource ds = (DataSource) envContext.lookup(RESOURCE_NAME);
		// conn = ds.getConnection();
		// } catch (NamingException e) {
		// e.printStackTrace();
		// throw new EDMException(e);
		// } catch (SQLException e) {
		// e.printStackTrace();
		// throw new EDMException(e);
		// }
	}

	protected void prepareStatement(String sql) throws EDMException {
		try {
			ps = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new EDMException(e);
		}
	}

	/**
	 * In this method, set the value into the parameters. Make sure it is
	 * following the field name order.<br>
	 * Etc:<br>
	 * ps.setString( 1, "Hello World" );
	 * 
	 * @throws EDMException
	 */
	public abstract void prepareUpdate() throws EDMException;

	protected String questionMarks() {
		String temp = "";
		int fieldlength = fieldnames().split(",").length;
		for (int i = 0; i < fieldlength; i++) {
			temp = temp + "?";
			if (i < fieldlength - 1) {
				temp = temp + ", ";
			}
		}
		return temp;
	}

	public void save() throws EDMException {
		if (this.id > -1) {
			logger.debug("Record already exist, calling update()");
			this.update();
			return;
		}
		try {
			logger.debug("Saving new record.");
			openConnection();
			prepareStatement(savequery());
			prepareUpdate();
			executeUpdate();
			// Works on HSQLDB only
			prepareStatement("SELECT IDENTITY() FROM user");
			executeQuery();
			if (resultset.next()) {
				this.id = resultset.getLong(1);
			}
		} catch (SQLException e) {
			throw new EDMException(e);
		} finally {
			closeStatement();
		}
	}

	/**
	 * 
	 * @return "INSERT INTO " + tablename() + "(" + fieldnames() + ") " +
	 *         "VALUES (" + questionMarks() + ")";
	 */
	protected String savequery() {
		return "INSERT INTO " + tablename() + "(" + fieldnames() + ") "
				+ "VALUES (" + questionMarks() + ")";
	}

	/**
	 * 
	 * @return "SELECT " + fieldnames() + " FROM " + tablename() +
	 *         " WHERE id = " + id;
	 */
	protected String selectquery() {
		return "SELECT " + fieldnames() + " FROM " + tablename()
				+ " WHERE id = " + id;
	}

	protected String selectlistquery(String criteria) {
		if(criteria == null){
			return "SELECT id," + fieldnames() + " FROM " + tablename();
		}
		if(criteria.trim().length()==0){
			return "SELECT id," + fieldnames() + " FROM " + tablename();
		}
		return "SELECT id," + fieldnames() + " FROM " + tablename() + " WHERE "
				+ criteria;
	}

	public void setId(long id) {
		this.id = id;
	}

	/**
	 * Tabble name
	 * 
	 * @return
	 */
	protected abstract String tablename();

	public void update() throws EDMException {
		try {
			openConnection();
			prepareStatement(updatequery());
			prepareUpdate();
			executeUpdate();
		} finally {
			closeStatement();
		}
	}

	/**
	 * 
	 * @return "UPDATE " + tablename() + " SET " + updateQuestionMarks() +
	 *         " WHERE id = " + id;
	 */
	protected String updatequery() {
		return "UPDATE " + tablename() + " SET " + updateQuestionMarks()
				+ " WHERE id = " + id;
	}

	protected String updateQuestionMarks() {
		String temp = "";
		int fieldlength = fieldnames().split(",").length;
		for (int i = 0; i < fieldlength; i++) {
			temp = temp + fieldnames().split(",")[i] + " = ?";
			if (i < fieldlength - 1) {
				temp = temp + ", ";
			}
		}
		return temp;
	}
	
	protected String specificFieldName(){
		String sfieldname = "";
		String[] fields = fieldnames().split(",");
		for (int i=0; i<fields.length; i++) {
			sfieldname += tablename() + "." + fields[i].trim();
			if( i < fields.length - 1 ){
				sfieldname += ",";
			}
		}
		return sfieldname;
	}

}